package com.tansun.easycare.rule.utils;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.openxml4j.opc.OPCPackage;
import org.apache.poi.openxml4j.opc.PackageAccess;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.util.SAXHelper;
import org.apache.poi.xssf.eventusermodel.ReadOnlySharedStringsTable;
import org.apache.poi.xssf.eventusermodel.XSSFReader;
import org.apache.poi.xssf.eventusermodel.XSSFSheetXMLHandler;
import org.apache.poi.xssf.model.StylesTable;
import org.apache.poi.xssf.streaming.SXSSFRow;
import org.apache.poi.xssf.streaming.SXSSFSheet;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.xml.sax.InputSource;
import org.xml.sax.XMLReader;

import com.tansun.easycare.core.config.Global;
import com.tansun.easycare.rule.ruletest.entity.RuleTest;
import com.tansun.rule.utils.MClass;
import com.tansun.rule.utils.MField;

/**
 * 大数据量excel读写
 * @author xuconghui
 *
 */
public class BigExcelUtils {
	
	public static void readExcel(String excelpath,MapSheetContentsHandler handler) {
		OPCPackage pkg = null;  
		InputStream sheetInputStream = null;
		try {
		    pkg = OPCPackage.open(excelpath, PackageAccess.READ);  
		    XSSFReader xssfReader = new XSSFReader(pkg);  
		      
		    StylesTable styles = xssfReader.getStylesTable();   
		    ReadOnlySharedStringsTable strings = new ReadOnlySharedStringsTable(pkg);  
		    sheetInputStream = xssfReader.getSheetsData().next();  
		    
		    XMLReader sheetParser = SAXHelper.newXMLReader();
		    sheetParser.setContentHandler(new XSSFSheetXMLHandler(styles, strings, handler, false));  
		    sheetParser.parse(new InputSource(sheetInputStream));
		} catch (Exception e) {  
			if(!MapSheetContentsHandler.BREAK_INFO.equals(e.getMessage())) {
				throw new RuntimeException(e.getMessage(), e); 
			}
		}finally {  
		    if(sheetInputStream != null){  
		        try {  
		            sheetInputStream.close();  
		        } catch (IOException e) {  
		            throw new RuntimeException(e.getMessage(), e);  
		        }  
		    }  
		    if(pkg != null){  
		        try {  
		            pkg.close();  
		        } catch (IOException e) {  
		            throw new RuntimeException(e.getMessage(), e);  
		        }  
		    }  
		}
	}
	
	@SuppressWarnings({ "resource"})
	public static void writeExcel(List<Map<String, Object>> resultList,Map<String, MClass> metaMap,RuleTest ruleTest){
		//保存路径
        String resultBasePath = Global.getConfig("file.uploadRoot") + File.separator + ruleTest.getProjectCode() + ruleTest.getResultPath();
        File f = new File(resultBasePath);
        if (!f.exists()) f.mkdirs();
        String excelPath = resultBasePath + File.separator + ruleTest.getId() +".xlsx";
        
		SXSSFWorkbook sxssfWorkbook = null;
		BufferedOutputStream outputStream = null;
		try {
			//这样表示SXSSFWorkbook只会保留100条数据在内存中，其它的数据都会写到磁盘里，这样的话占用的内存就会很少
			sxssfWorkbook = new SXSSFWorkbook(getXSSFWorkbook(excelPath),1000);
			CellStyle style = sxssfWorkbook.createCellStyle();
	        style.setAlignment(CellStyle.ALIGN_CENTER);//样式居中
	        
			//获取第一个Sheet页
			 SXSSFSheet sheet = (SXSSFSheet) sxssfWorkbook.getSheetAt(0);
			 
			 //获取模型信息
			 List<List<String[]>> modelInfo = getModelInfo(resultList.get(0),metaMap,ruleTest);
			 
			 writeModelInfo(sheet,modelInfo);
			 
			 //写入数据信息从第4行开始
			 writeRowGT3(sheet,resultList,modelInfo);
			 outputStream = new BufferedOutputStream(new FileOutputStream(excelPath));
			 sxssfWorkbook.write(outputStream);
			 outputStream.flush();
			 sxssfWorkbook.dispose();// 释放workbook所占用的所有windows资源
		} catch (IOException e) {
			e.printStackTrace();
		}finally {
			if(outputStream!=null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
	}
	/**
	 * 写入信息头
	 * @param sheet
	 * @param modelInfo
	 */
	public static void writeModelInfo(SXSSFSheet sheet,List<List<String[]>> modelInfo) {
		for(int i=0;i<modelInfo.size();i++) {
			List<String[]> list = modelInfo.get(i);
			SXSSFRow row = (SXSSFRow) sheet.createRow(i);
			int num = 0;
			for(int j=0;j<list.size();j++) {
				String[] strArr = list.get(j);
				if(StringUtils.isNotEmpty(strArr[3])) {
					row.createCell(num).setCellValue(strArr[2]);
					sheet.addMergedRegion(new CellRangeAddress(i, i, num, Integer.parseInt(strArr[3])+num-1));
					num += Integer.parseInt(strArr[3]);
				}else {
					row.createCell(j).setCellValue(strArr[2]);
					num +=1;
				}
			}
		}
		
	}
	/**
	 * 写入结果数据从第4行开始
	 * @param sheet
	 * @param resultList
	 * @param modelInfo
	 */
	@SuppressWarnings("unchecked")
	public static void writeRowGT3(SXSSFSheet sheet,List<Map<String, Object>> resultList,List<List<String[]>> modelInfo) {
		List<String[]> dataInfo = modelInfo.get(3);
		 for(int i=4;i<resultList.size();i++) {
			 SXSSFRow row = (SXSSFRow) sheet.createRow(i);
			 Map<String, Object> dataMap = resultList.get(i);
			 for(int j=0;j<dataInfo.size();j++) {
				 String[] strArr = dataInfo.get(j);
				 String className = strArr[0];
				 String paramName = strArr[1];
				 if(dataMap.containsKey(className)) {
					 Map<String,Object> childMap = (Map<String,Object>)dataMap.get(className);
					 Object val = childMap.get(paramName);
					 if(val instanceof List) {
						 row.createCell(j).setCellValue(StringUtils.join((List)val,","));
					 }else if(val == null) {
						 row.createCell(j).setCellValue("null");
					 }else {
						 row.createCell(j).setCellValue(val.toString());
					 }
					  
				 }else {
					 row.createCell(j).setCellValue("");
				 }
			 }
		 }
	}
	@SuppressWarnings("unchecked")
	public static List<List<String[]>> getModelInfo(Map<String, Object> dataMap,Map<String, MClass> metaMap,RuleTest ruleTest){
		List<List<String[]>> listStrArr = new ArrayList<List<String[]>>();
		//row = 0 的数据格式
		List<String[]> list0 = new ArrayList<String[]>();
		list0.add(new String[] {null,null,ExcelConstant.TYPE_CELL+ruleTest.getModelTypeName(),null});
		list0.add(new String[] {null,null,ExcelConstant.MODEL_CELL+ruleTest.getRuleName(),null});
		list0.add(new String[] {null,null,ExcelConstant.VERSION_CELL+ruleTest.getVersion(),null});
		list0.add(new String[] {null,null,ExcelConstant.DATA_CELL+ruleTest.getId(),null});
		
		//row = 1 的数据格式
		List<String[]> list1 = new ArrayList<String[]>();
		list1.add(new String[] {null,null,"",null});
		//row = 2 的数据格式
		List<String[]> list2 = new ArrayList<String[]>();
		list2.add(new String[] {null,null,"对象",null});
		//row = 3 的数据格式
		List<String[]> list3 = new ArrayList<String[]>();
		list3.add(new String[] {null,null,"属性",null});
		int count = 0;
		for(String className:metaMap.keySet()) {
			if(dataMap.containsKey(className)) {
				Map<String, Object> childMap = (Map<String, Object>) dataMap.get(className);
	    		MClass mclass = metaMap.get(className);
				Map<String,MField> fieldMap = mclass.getFieldMap();
				int num = 0;
				for(String paramName:fieldMap.keySet()) {
					if(childMap.containsKey(paramName)) {
						MField mField = fieldMap.get(paramName);
						String dmpkey = mclass.getChzn()+"."+mField.getChzn();
						list3.add(new String[] {className,paramName,dmpkey,null});
						num += 1;
					}
				}
				count += num;
				list2.add(new String[] {className,null,mclass.getChzn(),num+""});
			}
    	}
		list1.add(new String[] {null,null,ExcelConstant.RESULT_CELL,count+""});
		list0.add(new String[] {null,null,ExcelConstant.START_TESTTIME_CELL+ExcelConstant.spf.format(ruleTest.getBeginDate()),null});
		list0.add(new String[] {null,null,ExcelConstant.END_TESTTIME_CELL+ExcelConstant.spf.format(ruleTest.getEndDate()),null});
		list0.add(new String[] {null,null,ExcelConstant.ALL_TESTTIME_CELL+(ruleTest.getEndDate().getTime() - ruleTest.getBeginDate().getTime())+"ms",null});
		listStrArr.add(list0);
		listStrArr.add(list1);
		listStrArr.add(list2);
		listStrArr.add(list3);
		return listStrArr;
	}
	/**
	 * 先创建一个XSSFWorkbook对象
	 * @param excelPath
	 * @return
	 */
	public static XSSFWorkbook getXSSFWorkbook(String excelPath) {
		XSSFWorkbook workbook =  null;
		BufferedOutputStream outputStream = null;
		try {
			outputStream = new BufferedOutputStream(new FileOutputStream(excelPath));
			workbook = new XSSFWorkbook();
			workbook.createSheet("Sheet1");
			workbook.write(outputStream);
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(outputStream!=null) {
				try {
					outputStream.close();
				} catch (IOException e) {
					e.printStackTrace();
				}
			}
		}
		return workbook;
	}
}
